package com.research.demo.toolkit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp2.*;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.pool2.impl.GenericObjectPool;


/**
 *
 * 连接数据库的综合类。
 * 依赖jar包：commons.dbcp2-2.1.1，commons.dbutils-1.6，commons.pool2-2.4.2包。
 *
 * @author simba
 * @date 2017年06月18日
 */

class DBHelper {
    private String dri = null;
    private String url = null;
    private String username = null;
    private String password = null;
    private String poolName = null; // 连接池名称
    private GenericObjectPool<PoolableConnection> connectionPool = null; // 连接池
    // 对应的定时查询类
    private QueryThread queryThread = null;

    /**
     * 功能：构造函数
     *
     * @param dri
     *            驱动全类名，例如：com.mysql.jdbc.Driver。
     * @param url
     *            数据库url连接，例如：
     *            "jdbc:mysql://127.0.0.1:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8"
     * @param userName
     *            数据库用户名，例如：root
     * @param password
     *            数据库密码，例如：abc
     * @param poolName
     *            创建的数据库连接池的名称，例如mypool，注意一个web容器此名称不能重复。
     */
    DBHelper(String dri, String url, String userName, String password,
             String poolName) {
        this.dri = dri;
        this.url = url;
        this.username = userName;
        this.password = password;
        this.poolName = poolName;
    }


    /**
     * 查询sql语句。
     *
     * @param sql
     *            被执行的sql语句
     * @return List<Map<String,Object>>
     * @throws SQLException
     */
    List<Map<String, Object>> query(String sql) throws SQLException {
        List<Map<String, Object>> results = null;
        Connection conn = null;
        try {
            conn = getConnection();
            QueryRunner qr = new QueryRunner();
            results = qr.query(conn, sql, new MapListHandler());
        } finally {
            Close(conn);
        }
        return results;
    }

    /**
     * 根据参数查询sql语句
     *
     * @param sql
     *            sql语句
     * @param param
     *            参数
     * @return List<Map<String,Object>>
     * @throws SQLException
     */
    public List<Map<String, Object>> query(String sql, Object[] param) throws SQLException {
        List<Map<String, Object>> results = null;
        Connection conn = null;
        try {
            conn = getConnection();
            QueryRunner qr = new QueryRunner();
            results = qr.query(conn, sql, new MapListHandler(), param);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Close(conn);
        }
        return results;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     *            被执行的sql语句
     * @return 受影响的行
     * @throws Exception
     */
    public int execute(String sql) throws SQLException {
        Connection conn = getConnection();
        int rows = 0;
        try {
            QueryRunner qr = new QueryRunner();
            rows = qr.update(conn, sql);
        } finally {
            Close(conn);
        }
        return rows;
    }


    /**
     * 执行含参数的sql语句
     *
     * @param sql
     *            被执行的sql语句
     * @param params
     *            参数
     * @return 返回受影响的行
     * @throws Exception
     */
    public int execute(String sql, Object[] params) throws SQLException {
        Connection conn = getConnection();
        int rows = 0;
        try {
            QueryRunner qr = new QueryRunner();
            rows = qr.update(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Close(conn);
        }
        return rows;
    }

    public int[] batch(String sql, Object[][] params)  throws SQLException {
        Connection conn = getConnection();
        int[] rows = null;
        try {
            QueryRunner qr = new QueryRunner();
            rows = qr.batch(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Close(conn);
        }
        return rows;
    }

    /**
     * 关闭连接
     *
     * @param conn
     * @throws SQLException
     */
    private void Close(Connection conn) throws SQLException {
        DbUtils.closeQuietly(conn);
    }

    /**
     * 启动连接池
     *
     */
    private void StartPool() {
        try {
            Class.forName(dri);
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
        if (connectionPool != null) {
            ShutdownPool();
        }
        try {
            // 1. Register the Driver to the jbdc.driver java property
            PoolConnectionFactory
                    .registerJDBCDriver(dri);
            // 2. Create the Connection Factory (DriverManagerConnectionFactory)
            ConnectionFactory connectionFactory = PoolConnectionFactory
                    .getConnFactory(url, username, password);
            // 3. Instantiate the Factory of Pooled Objects
            PoolableConnectionFactory poolfactory = new PoolableConnectionFactory(
                    connectionFactory, null);

            // 4. Create the Pool with the PoolableConnection objects
            connectionPool = new GenericObjectPool(poolfactory);

            // 5. Set the objectPool to enforces the association (prevent bugs)
            poolfactory.setPool(connectionPool);

            // 6. Get the Driver of the pool and register them
            PoolConnectionFactory.getDBCPDriver();
            PoolConnectionFactory.registerPool(poolName, connectionPool);
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 开启查询程序
        queryThread = new QueryThread(this);
        queryThread.start();
    }

    /**
     * 关闭连接池
     *
     */
    private void ShutdownPool() {
        try {
            PoolingDriver driver = (PoolingDriver) DriverManager
                    .getDriver("jdbc:apache:commons:dbcp:");
            driver.closePool(poolName);

            // 关闭定时查询
            queryThread.setStartQuery(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 得到一个连接
     *
     */
    private synchronized Connection getConnection() {
        Connection conn = null;
        try {
            if (connectionPool == null) {
                StartPool();
            }
            conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + poolName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

